{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d09ea88",
   "metadata": {},
   "outputs": [
    {
     "ename": "",
     "evalue": "",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31mRunning cells with 'flask (Python 3.10.16)' requires the ipykernel package.\n",
      "\u001b[1;31m<a href='command:jupyter.createPythonEnvAndSelectController'>Create a Python Environment</a> with the required packages."
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import re\n",
    "\n",
    "# 读取 Excel 文件\n",
    "df = pd.read_excel(\"动态成本.xlsx\")\n",
    "\n",
    "# 只保留科目名称列并去除空白行\n",
    "df = df[['科目名称']].dropna().copy()\n",
    "\n",
    "\n",
    "def parse_row(row, indent_width=2):\n",
    "    text = str(row).rstrip()  # 保留行首空格用于缩进判断，去除右侧空格\n",
    "    \n",
    "    # 匹配：编码在括号内，如 开发成本(A.01)\n",
    "    match = re.match(r'^[\\s　-]*([\\[\\]\\+\\-]*)?(.+?)\\((A(?:\\.\\d+)+)\\)', text)\n",
    "    if match:\n",
    "        name = match.group(2).strip()\n",
    "        code = match.group(3).strip()\n",
    "        level = code.count('.') + 1\n",
    "        return level, code, name\n",
    "\n",
    "    # 匹配：只有编码（比如最末级），如 A.01.01\n",
    "    match_code_only = re.match(r'^[\\s　-]*([\\[\\]\\+\\-]*)?([A-Z]?[A-Z]?(?:\\.\\d+)+)', text)\n",
    "    if match_code_only:\n",
    "        code = match_code_only.group(2).strip()\n",
    "        level = code.count('.') + 1\n",
    "        return level, code, code\n",
    "\n",
    "    # 否则：无编码，根据缩进判断层级\n",
    "    leading_spaces = len(re.match(r'^[\\s　]*', text).group().replace('　', '  '))  # 全角空格转为两个半角\n",
    "    level = (leading_spaces // indent_width) + 1 if leading_spaces >= 0 else None\n",
    "    name = text.strip()\n",
    "    return level, None, name\n",
    "\n",
    "\n",
    "# 解析层级结构\n",
    "parsed = df['科目名称'].apply(parse_row)\n",
    "df['level'] = parsed.apply(lambda x: x[0])\n",
    "df['code'] = parsed.apply(lambda x: x[1])\n",
    "df['name'] = parsed.apply(lambda x: x[2])\n",
    "\n",
    "# 构建多级路径字段（level_1, level_2, ..., full_path）\n",
    "hierarchy_cols = ['level_1', 'level_2', 'level_3', 'level_4']\n",
    "parents = [''] * 10  # 假设最多10层\n",
    "\n",
    "level_names = []\n",
    "\n",
    "for idx, row in df.iterrows():\n",
    "    level = row['level']\n",
    "    name = row['name']\n",
    "\n",
    "    if level is not None:\n",
    "        level = int(level)\n",
    "        parents[level - 1] = name\n",
    "        # 清空更深的层级\n",
    "        for i in range(level, len(parents)):\n",
    "            parents[i] = ''\n",
    "    df.at[idx, 'full_path'] = ' > '.join([p for p in parents if p])\n",
    "    for i in range(len(hierarchy_cols)):\n",
    "        df.at[idx, hierarchy_cols[i]] = name if i == (level - 1) else None\n",
    "\n",
    "\n",
    "# 输出结构化结果\n",
    "df_structured = df[['科目名称', 'code', 'name', 'level'] + hierarchy_cols + ['full_path']]\n",
    "print(df_structured.head())\n",
    "\n",
    "# 保存为结构化 Excel\n",
    "df_structured.to_excel(\"结构化_科目层级.xlsx\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9fac7562",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import re\n",
    "import uuid\n",
    "from datetime import datetime\n",
    "from config import Config\n",
    "from sqlalchemy import create_engine, Column, String, Integer, Float, DateTime\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "\n",
    "# ------------------ 配置数据库连接 ------------------\n",
    "DB_URI = Config.SQLALCHEMY_DATABASE_URI\n",
    "TABLE_NAME = \"subject_items\"\n",
    "\n",
    "engine = create_engine(DB_URI)\n",
    "Base = declarative_base()\n",
    "\n",
    "# ------------------ 定义ORM模型 ------------------\n",
    "class SubjectItem(Base):\n",
    "    __tablename__ = TABLE_NAME\n",
    "    id = Column(String(36), primary_key=True)  # UUID\n",
    "    name = Column(String(255))\n",
    "    code = Column(String(50))\n",
    "    level = Column(Integer)\n",
    "    full_path = Column(String(500))\n",
    "    level_1 = Column(String(255))\n",
    "    level_2 = Column(String(255))\n",
    "    level_3 = Column(String(255))\n",
    "    level_4 = Column(String(255))\n",
    "    level_5 = Column(String(255))\n",
    "    amount = Column(Float)\n",
    "    created_at = Column(DateTime, default=datetime.now)\n",
    "\n",
    "# ------------------ 创建表（如果不存在） ------------------\n",
    "Base.metadata.create_all(engine)\n",
    "Session = sessionmaker(bind=engine)\n",
    "session = Session()\n",
    "\n",
    "# ------------------ 读取并解析 Excel ------------------\n",
    "df = pd.read_excel(\"动态成本.xlsx\")[['科目名称']].dropna().copy()\n",
    "\n",
    "def parse_row(row, indent_width=2):\n",
    "    text = str(row).rstrip()\n",
    "    match = re.match(r'^[\\s　-]*([\\[\\]\\+\\-]*)?(.+?)\\((A(?:\\.\\d+)+)\\)', text)\n",
    "    if match:\n",
    "        name = match.group(2).strip()\n",
    "        code = match.group(3).strip()\n",
    "        level = code.count('.') + 1\n",
    "        return level, code, name\n",
    "    match_code_only = re.match(r'^[\\s　-]*([\\[\\]\\+\\-]*)?([A-Z]?[A-Z]?(?:\\.\\d+)+)', text)\n",
    "    if match_code_only:\n",
    "        code = match_code_only.group(2).strip()\n",
    "        level = code.count('.') + 1\n",
    "        return level, code, code\n",
    "    leading_spaces = len(re.match(r'^[\\s　]*', text).group().replace('　', '  '))\n",
    "    level = (leading_spaces // indent_width) + 1\n",
    "    name = text.strip()\n",
    "    return level, None, name\n",
    "\n",
    "parsed = df['科目名称'].apply(parse_row)\n",
    "df['level'] = parsed.apply(lambda x: x[0])\n",
    "df['code'] = parsed.apply(lambda x: x[1])\n",
    "df['name'] = parsed.apply(lambda x: x[2])\n",
    "\n",
    "# 构建多级路径字段\n",
    "hierarchy_cols = ['level_1', 'level_2', 'level_3', 'level_4', 'level_5']\n",
    "parents = [''] * 10\n",
    "for idx, row in df.iterrows():\n",
    "    level = row['level']\n",
    "    name = row['name']\n",
    "    if level is not None:\n",
    "        level = int(level)\n",
    "        parents[level - 1] = name\n",
    "        for i in range(level, len(parents)):\n",
    "            parents[i] = ''\n",
    "    df.at[idx, 'full_path'] = ' > '.join([p for p in parents if p])\n",
    "    for i in range(len(hierarchy_cols)):\n",
    "        df.at[idx, hierarchy_cols[i]] = name if i == (level - 1) else None\n",
    "\n",
    "# ------------------ 清洗数值列（如果有） ------------------\n",
    "def clean_number(val):\n",
    "    if pd.isna(val): return None\n",
    "    try:\n",
    "        return float(str(val).replace(',', '').strip())\n",
    "    except:\n",
    "        return None\n",
    "\n",
    "# 示例添加一个金额列（如你有 “金额/预算/成本” 等列）\n",
    "# df['amount'] = df['金额'].apply(clean_number)  # 如果有的话\n",
    "df['amount'] = None  # 如果暂时没有金额列，可保留结构\n",
    "\n",
    "# ------------------ 写入数据库 ------------------\n",
    "records = []\n",
    "for _, row in df.iterrows():\n",
    "    item = SubjectItem(\n",
    "        id=str(uuid.uuid4()),\n",
    "        name=row['name'],\n",
    "        code=row['code'],\n",
    "        level=row['level'],\n",
    "        full_path=row['full_path'],\n",
    "        level_1=row['level_1'],\n",
    "        level_2=row['level_2'],\n",
    "        level_3=row['level_3'],\n",
    "        level_4=row['level_4'],\n",
    "        level_5=row['level_5'],\n",
    "        amount=row['amount'],\n",
    "        created_at=datetime.utcnow()\n",
    "    )\n",
    "    records.append(item)\n",
    "\n",
    "session.bulk_save_objects(records)\n",
    "session.commit()\n",
    "print(f\"✅ 成功导入 {len(records)} 条数据到数据库表 `{TABLE_NAME}`。\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "42017a0c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import re\n",
    "import uuid\n",
    "from datetime import datetime\n",
    "from sqlalchemy import create_engine, Column, String, Integer, DateTime\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "import chromadb\n",
    "from chromadb.config import Settings\n",
    "from sentence_transformers import SentenceTransformer\n",
    "from fastapi import FastAPI, Query\n",
    "from typing import List\n",
    "\n",
    "# -------- 配置 --------\n",
    "DB_URI = \"mysql+pymysql://username:password@localhost:3306/your_db?charset=utf8mb4\"\n",
    "engine = create_engine(DB_URI)\n",
    "Base = declarative_base()\n",
    "Session = sessionmaker(bind=engine)\n",
    "session = Session()\n",
    "\n",
    "# -------- 数据模型 --------\n",
    "class SubjectItem(Base):\n",
    "    __tablename__ = \"subject_items\"\n",
    "    id = Column(String(36), primary_key=True)  # UUID\n",
    "    name = Column(String(255))\n",
    "    code = Column(String(50))\n",
    "    level = Column(Integer)\n",
    "    full_path = Column(String(1000))\n",
    "    level_1 = Column(String(255))\n",
    "    level_2 = Column(String(255))\n",
    "    level_3 = Column(String(255))\n",
    "    level_4 = Column(String(255))\n",
    "    level_5 = Column(String(255))\n",
    "    created_at = Column(DateTime, default=datetime.now)\n",
    "\n",
    "Base.metadata.create_all(engine)\n",
    "\n",
    "# -------- 解析函数 --------\n",
    "def parse_row(row, indent_width=2):\n",
    "    text = str(row).rstrip()\n",
    "    match = re.match(r'^[\\s　-]*([\\[\\]\\+\\-]*)?(.+?)\\((A(?:\\.\\d+)+)\\)', text)\n",
    "    if match:\n",
    "        name = match.group(2).strip()\n",
    "        code = match.group(3).strip()\n",
    "        level = code.count('.') + 1\n",
    "        return level, code, name\n",
    "    match_code_only = re.match(r'^[\\s　-]*([\\[\\]\\+\\-]*)?([A-Z]?[A-Z]?(?:\\.\\d+)+)', text)\n",
    "    if match_code_only:\n",
    "        code = match_code_only.group(2).strip()\n",
    "        level = code.count('.') + 1\n",
    "        return level, code, code\n",
    "    leading_spaces = len(re.match(r'^[\\s　]*', text).group().replace('　', '  '))\n",
    "    level = (leading_spaces // indent_width) + 1\n",
    "    name = text.strip()\n",
    "    return level, None, name\n",
    "\n",
    "# -------- 初始化 ChromaDB 和向量模型 --------\n",
    "chroma_client = chromadb.Client(Settings(chroma_db_impl=\"duckdb+parquet\", persist_directory=\"./chromadb_data\"))\n",
    "\n",
    "collection_name = \"subject_items_collection\"\n",
    "if collection_name in [c.name for c in chroma_client.list_collections()]:\n",
    "    collection = chroma_client.get_collection(collection_name)\n",
    "else:\n",
    "    collection = chroma_client.create_collection(name=collection_name)\n",
    "\n",
    "embedder = SentenceTransformer(\"all-MiniLM-L6-v2\")\n",
    "\n",
    "# -------- 导入函数 --------\n",
    "def import_data_from_excel(file_path):\n",
    "    df = pd.read_excel(file_path)[['科目名称']].dropna().copy()\n",
    "    parsed = df['科目名称'].apply(parse_row)\n",
    "    df['level'] = parsed.apply(lambda x: x[0])\n",
    "    df['code'] = parsed.apply(lambda x: x[1])\n",
    "    df['name'] = parsed.apply(lambda x: x[2])\n",
    "\n",
    "    hierarchy_cols = ['level_1', 'level_2', 'level_3', 'level_4', 'level_5']\n",
    "    parents = [''] * 10\n",
    "    for idx, row in df.iterrows():\n",
    "        level = row['level']\n",
    "        name = row['name']\n",
    "        if level is not None:\n",
    "            level = int(level)\n",
    "            parents[level - 1] = name\n",
    "            for i in range(level, len(parents)):\n",
    "                parents[i] = ''\n",
    "        df.at[idx, 'full_path'] = ' > '.join([p for p in parents if p])\n",
    "        for i in range(len(hierarchy_cols)):\n",
    "            df.at[idx, hierarchy_cols[i]] = name if i == (level - 1) else None\n",
    "\n",
    "    db_records = []\n",
    "    chromadb_ids = []\n",
    "    texts_to_embed = []\n",
    "\n",
    "    for _, row in df.iterrows():\n",
    "        uid = str(uuid.uuid4())\n",
    "        db_records.append(\n",
    "            SubjectItem(\n",
    "                id=uid,\n",
    "                name=row['name'],\n",
    "                code=row['code'],\n",
    "                level=row['level'],\n",
    "                full_path=row['full_path'],\n",
    "                level_1=row['level_1'],\n",
    "                level_2=row['level_2'],\n",
    "                level_3=row['level_3'],\n",
    "                level_4=row['level_4'],\n",
    "                level_5=row['level_5'],\n",
    "                created_at=datetime.now()\n",
    "            )\n",
    "        )\n",
    "        chromadb_ids.append(uid)\n",
    "        texts_to_embed.append(row['full_path'])\n",
    "\n",
    "    session.bulk_save_objects(db_records)\n",
    "    session.commit()\n",
    "\n",
    "    embeddings = embedder.encode(texts_to_embed, show_progress_bar=True)\n",
    "    collection.add(\n",
    "        documents=texts_to_embed,\n",
    "        embeddings=embeddings.tolist(),\n",
    "        ids=chromadb_ids\n",
    "    )\n",
    "    print(f\"✅ 成功导入 {len(db_records)} 条数据\")\n",
    "\n",
    "# -------- 查询示例 --------\n",
    "app = FastAPI()\n",
    "\n",
    "@app.get(\"/query_by_name/\")\n",
    "def query_by_name(name: str):\n",
    "    # 结构化数据库查询\n",
    "    results = session.query(SubjectItem).filter(SubjectItem.name.like(f\"%{name}%\")).all()\n",
    "    return [{\"id\": r.id, \"name\": r.name, \"full_path\": r.full_path, \"level\": r.level} for r in results]\n",
    "\n",
    "@app.get(\"/query_by_semantic/\")\n",
    "def query_by_semantic(text: str, top_k: int = 5):\n",
    "    # 先生成查询向量\n",
    "    query_embedding = embedder.encode([text])[0]\n",
    "    results = collection.query(query_embeddings=[query_embedding], n_results=top_k)\n",
    "    ids = results['ids'][0]\n",
    "    docs = results['documents'][0]\n",
    "    return [{\"id\": i, \"full_path\": d} for i, d in zip(ids, docs)]\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    import sys\n",
    "    if len(sys.argv) > 1:\n",
    "        import_data_from_excel(sys.argv[1])\n",
    "    else:\n",
    "        print(\"请提供Excel文件路径作为参数，例如 python this_script.py 动态成本.xlsx\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "660542ff",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "2d471629",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "数据已成功导入数据库\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import re\n",
    "import uuid\n",
    "from datetime import datetime\n",
    "from config import Config\n",
    "from sqlalchemy import create_engine, Column, String, Integer, Float, DateTime\n",
    "from sqlalchemy.orm import declarative_base, sessionmaker\n",
    "\n",
    "# --- 配置数据库连接 ---\n",
    "# 这里用MySQL示例，改成你实际的连接信息\n",
    "DATABASE_URL = Config.SQLALCHEMY_DATABASE_URI\n",
    "engine = create_engine(DATABASE_URL, echo=False)\n",
    "Session = sessionmaker(bind=engine)\n",
    "Base = declarative_base()\n",
    "\n",
    "\n",
    "# --- 定义数据表结构 ---\n",
    "class SubjectItem(Base):\n",
    "    __tablename__ = 'subject_items'\n",
    "\n",
    "    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))\n",
    "    code = Column(String(64), nullable=True)\n",
    "    name = Column(String(255), nullable=False)\n",
    "    level = Column(Integer, nullable=True)\n",
    "\n",
    "    # 金额字段，浮点数\n",
    "    amount_prev = Column(Float, default=0.0)\n",
    "    amount_curr = Column(Float, default=0.0)\n",
    "\n",
    "    # 层级路径，方便查询\n",
    "    full_path = Column(String(1000), nullable=True)\n",
    "\n",
    "    # 自动创建时间\n",
    "    created_at = Column(DateTime, default=datetime.now)\n",
    "\n",
    "\n",
    "# --- 工具函数：解析行，返回层级、编码、名称 ---\n",
    "def parse_row(text, indent_width=2):\n",
    "    text = str(text).rstrip()  # 保留前导空格用于判断缩进\n",
    "\n",
    "    # 匹配编码在括号内，如：开发成本(A.01)\n",
    "    match = re.match(r'^[\\s　-]*([\\[\\]\\+\\-]*)?(.+?)\\((A(?:\\.\\d+)+)\\)', text)\n",
    "    if match:\n",
    "        name = match.group(2).strip()\n",
    "        code = match.group(3).strip()\n",
    "        level = code.count('.') + 1\n",
    "        return level, code, name\n",
    "\n",
    "    # 只有编码，比如 A.01.01\n",
    "    match_code_only = re.match(r'^[\\s　-]*([\\[\\]\\+\\-]*)?([A-Z]?[A-Z]?(?:\\.\\d+)+)', text)\n",
    "    if match_code_only:\n",
    "        code = match_code_only.group(2).strip()\n",
    "        level = code.count('.') + 1\n",
    "        return level, code, code\n",
    "\n",
    "    # 否则根据缩进计算层级，缩进0为最高层\n",
    "    leading_spaces = len(re.match(r'^[\\s　]*', text).group().replace('　', '  '))\n",
    "    level = (leading_spaces // indent_width) + 1 if leading_spaces >= 0 else None\n",
    "    name = text.strip()\n",
    "    return level, None, name\n",
    "\n",
    "\n",
    "# --- 工具函数：金额清洗，转 float ---\n",
    "def clean_amount(amount_str):\n",
    "    if pd.isna(amount_str):\n",
    "        return 0.0\n",
    "    try:\n",
    "        clean_str = str(amount_str).replace(',', '')\n",
    "        return float(clean_str)\n",
    "    except Exception:\n",
    "        return 0.0\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "def main():\n",
    "    # 读取Excel\n",
    "    df = pd.read_excel(\"动态成本.xlsx\")\n",
    "\n",
    "    # 清洗金额字段\n",
    "    df['amount_prev'] = df['上次签约金额'].apply(clean_amount)\n",
    "    df['amount_curr'] = df['本次签约金额'].apply(clean_amount)\n",
    "\n",
    "    # 解析层级结构\n",
    "    parsed = df['科目名称'].apply(parse_row)\n",
    "    df['level'] = parsed.apply(lambda x: x[0])\n",
    "    df['code'] = parsed.apply(lambda x: x[1])\n",
    "    df['name'] = parsed.apply(lambda x: x[2])\n",
    "\n",
    "    # 构建层级路径\n",
    "    parents = [''] * 10\n",
    "    full_paths = []\n",
    "\n",
    "    for idx, row in df.iterrows():\n",
    "        level = row['level']\n",
    "        name = row['name']\n",
    "\n",
    "        if level is not None:\n",
    "            level = int(level)\n",
    "            parents[level - 1] = name\n",
    "            # 清空更深层级\n",
    "            for i in range(level, len(parents)):\n",
    "                parents[i] = ''\n",
    "\n",
    "        full_path = ' > '.join([p for p in parents if p])\n",
    "        full_paths.append(full_path)\n",
    "\n",
    "    df['full_path'] = full_paths\n",
    "\n",
    "    # 创建表\n",
    "    Base.metadata.create_all(engine)\n",
    "\n",
    "    # 插入数据库\n",
    "    session = Session()\n",
    "    try:\n",
    "        for idx, row in df.iterrows():\n",
    "            item = SubjectItem(\n",
    "                code=row['code'],\n",
    "                name=row['name'],\n",
    "                level=row['level'],\n",
    "                amount_prev=row['amount_prev'],\n",
    "                amount_curr=row['amount_curr'],\n",
    "                full_path=row['full_path'],\n",
    "            )\n",
    "            session.add(item)\n",
    "        session.commit()\n",
    "        print(\"数据已成功导入数据库\")\n",
    "    except Exception as e:\n",
    "        session.rollback()\n",
    "        print(\"导入失败:\", e)\n",
    "    finally:\n",
    "        session.close()\n",
    "\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    main()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0f89a0a0",
   "metadata": {},
   "source": [
    "# 动态成本--层级"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "id": "3ef9d3c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# --- 工具函数：解析行，返回层级、编码、名称 ---\n",
    "def parse_row(text, indent_width=2):\n",
    "    text = str(text).rstrip()  # 保留前导空格用于判断缩进\n",
    "\n",
    "    # 匹配编码在括号内，如：开发成本(A.01)\n",
    "    match = re.match(r'^[\\s　-]*([\\[\\]\\+\\-]*)?(.+?)\\((A(?:\\.\\d+)+)\\)', text)\n",
    "    if match:\n",
    "        name = match.group(2).strip()\n",
    "        code = match.group(3).strip()\n",
    "        level = code.count('.') + 1\n",
    "        return level, code, name\n",
    "\n",
    "    # 只有编码，比如 A.01.01\n",
    "    match_code_only = re.match(r'^[\\s　-]*([\\[\\]\\+\\-]*)?([A-Z]?[A-Z]?(?:\\.\\d+)+)', text)\n",
    "    if match_code_only:\n",
    "        code = match_code_only.group(2).strip()\n",
    "        level = code.count('.') + 1\n",
    "        return level, code, code\n",
    "\n",
    "    # 否则根据缩进计算层级，缩进0为最高层\n",
    "    leading_spaces = len(re.match(r'^[\\s　]*', text).group().replace('　', '  '))\n",
    "    level = (leading_spaces // indent_width) + 1 if leading_spaces >= 0 else None\n",
    "    name = text.strip()\n",
    "    return level, None, name\n",
    "\n",
    "\n",
    "# --- 工具函数：金额清洗，转 float ---\n",
    "def clean_amount(amount_str):\n",
    "    if pd.isna(amount_str):\n",
    "        return 0.0\n",
    "    try:\n",
    "        clean_str = str(amount_str).replace(',', '')\n",
    "        return float(clean_str)\n",
    "    except Exception:\n",
    "        return 0.0\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "def main():\n",
    "    # 读取Excel\n",
    "    df = pd.read_excel(\"动态成本.xlsx\")\n",
    "\n",
    "    # 清洗金额字段\n",
    "    df['amount_prev'] = df['上次签约金额'].apply(clean_amount)\n",
    "    df['amount_curr'] = df['本次签约金额'].apply(clean_amount)\n",
    "\n",
    "    # 解析层级结构\n",
    "    parsed = df['科目名称'].apply(parse_row)\n",
    "    df['level'] = parsed.apply(lambda x: x[0])\n",
    "    df['code'] = parsed.apply(lambda x: x[1])\n",
    "    df['name'] = parsed.apply(lambda x: x[2])\n",
    "\n",
    "    # 构建层级路径\n",
    "    parents = [''] * 10\n",
    "    full_paths = []\n",
    "\n",
    "    for idx, row in df.iterrows():\n",
    "        level = row['level']\n",
    "        name = row['name']\n",
    "\n",
    "        if level is not None:\n",
    "            level = int(level)\n",
    "            parents[level - 1] = name\n",
    "            # 清空更深层级\n",
    "            for i in range(level, len(parents)):\n",
    "                parents[i] = ''\n",
    "\n",
    "        full_path = ' > '.join([p for p in parents if p])\n",
    "        full_paths.append(full_path)\n",
    "\n",
    "    df['full_path'] = full_paths\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "id": "30e731d0",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = main()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "id": "16e873b2",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>科目名称</th>\n",
       "      <th>类别</th>\n",
       "      <th>本次目标+调整成本</th>\n",
       "      <th>合同状态</th>\n",
       "      <th>合同有效签约金额</th>\n",
       "      <th>上次签约金额</th>\n",
       "      <th>本次签约金额</th>\n",
       "      <th>上次变更金额</th>\n",
       "      <th>本次变更金额</th>\n",
       "      <th>上次在途成本</th>\n",
       "      <th>...</th>\n",
       "      <th>上次待发生合约规划</th>\n",
       "      <th>本次待发生合约规划</th>\n",
       "      <th>上次动态成本</th>\n",
       "      <th>本次动态成本</th>\n",
       "      <th>amount_prev</th>\n",
       "      <th>amount_curr</th>\n",
       "      <th>level</th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>full_path</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>[-]项目成本(A)</td>\n",
       "      <td>科目</td>\n",
       "      <td>1,331,975,113.55</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>313,189,317.30</td>\n",
       "      <td>313,333,317.30</td>\n",
       "      <td>598,351.75</td>\n",
       "      <td>878,837.01</td>\n",
       "      <td>80,800.15</td>\n",
       "      <td>...</td>\n",
       "      <td>320,833,987.81</td>\n",
       "      <td>320,889,987.81</td>\n",
       "      <td>855805731.87</td>\n",
       "      <td>855805731.87</td>\n",
       "      <td>3.131893e+08</td>\n",
       "      <td>3.133333e+08</td>\n",
       "      <td>1</td>\n",
       "      <td>None</td>\n",
       "      <td>[-]项目成本(A)</td>\n",
       "      <td>[-]项目成本(A)</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>[-]开发成本(A.01)</td>\n",
       "      <td>科目</td>\n",
       "      <td>1,133,385,888.98</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>318,953,138.05</td>\n",
       "      <td>318,953,138.05</td>\n",
       "      <td>598,351.75</td>\n",
       "      <td>878,837.01</td>\n",
       "      <td>80,800.15</td>\n",
       "      <td>...</td>\n",
       "      <td>205,830,801.37</td>\n",
       "      <td>205,830,801.37</td>\n",
       "      <td>848198198.08</td>\n",
       "      <td>848198198.08</td>\n",
       "      <td>3.189531e+08</td>\n",
       "      <td>3.189531e+08</td>\n",
       "      <td>2</td>\n",
       "      <td>A.01</td>\n",
       "      <td>开发成本</td>\n",
       "      <td>[-]项目成本(A) &gt; 开发成本</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>[-]土地成本(A.01.01)</td>\n",
       "      <td>科目</td>\n",
       "      <td>9,189,388.90</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>9,189,388.90</td>\n",
       "      <td>9,189,388.90</td>\n",
       "      <td>9,189,388.90</td>\n",
       "      <td>9,189,388.90</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>3</td>\n",
       "      <td>A.01.01</td>\n",
       "      <td>土地成本</td>\n",
       "      <td>[-]项目成本(A) &gt; 开发成本 &gt; 土地成本</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>[+]政府土地费(A.01.01.01)</td>\n",
       "      <td>科目</td>\n",
       "      <td>9,189,388.90</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>9,189,388.90</td>\n",
       "      <td>9,189,388.90</td>\n",
       "      <td>9,189,388.90</td>\n",
       "      <td>9,189,388.90</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>4</td>\n",
       "      <td>A.01.01.01</td>\n",
       "      <td>政府土地费</td>\n",
       "      <td>[-]项目成本(A) &gt; 开发成本 &gt; 土地成本 &gt; 政府土地费</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>土地出让金</td>\n",
       "      <td>合约规划</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>...</td>\n",
       "      <td>1,339,711.38</td>\n",
       "      <td>1,339,711.38</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>0.000000e+00</td>\n",
       "      <td>9</td>\n",
       "      <td>None</td>\n",
       "      <td>土地出让金</td>\n",
       "      <td>[-]项目成本(A) &gt; 开发成本 &gt; 土地成本 &gt; 政府土地费 &gt; 土地出让金</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 25 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         科目名称    类别         本次目标+调整成本 合同状态 合同有效签约金额  \\\n",
       "0                  [-]项目成本(A)    科目  1,331,975,113.55  NaN      NaN   \n",
       "1             　　[-]开发成本(A.01)    科目  1,133,385,888.98  NaN      NaN   \n",
       "2        　　　　[-]土地成本(A.01.01)    科目      9,189,388.90  NaN      NaN   \n",
       "3  　　　　　　[+]政府土地费(A.01.01.01)    科目      9,189,388.90  NaN      NaN   \n",
       "4              　　　　　　　　 土地出让金  合约规划               NaN  NaN      NaN   \n",
       "\n",
       "           上次签约金额          本次签约金额      上次变更金额      本次变更金额     上次在途成本  ...  \\\n",
       "0  313,189,317.30  313,333,317.30  598,351.75  878,837.01  80,800.15  ...   \n",
       "1  318,953,138.05  318,953,138.05  598,351.75  878,837.01  80,800.15  ...   \n",
       "2            0.00            0.00        0.00        0.00       0.00  ...   \n",
       "3            0.00            0.00        0.00        0.00       0.00  ...   \n",
       "4             NaN             NaN         NaN         NaN        NaN  ...   \n",
       "\n",
       "        上次待发生合约规划       本次待发生合约规划        上次动态成本        本次动态成本   amount_prev  \\\n",
       "0  320,833,987.81  320,889,987.81  855805731.87  855805731.87  3.131893e+08   \n",
       "1  205,830,801.37  205,830,801.37  848198198.08  848198198.08  3.189531e+08   \n",
       "2    9,189,388.90    9,189,388.90  9,189,388.90  9,189,388.90  0.000000e+00   \n",
       "3    9,189,388.90    9,189,388.90  9,189,388.90  9,189,388.90  0.000000e+00   \n",
       "4    1,339,711.38    1,339,711.38           NaN           NaN  0.000000e+00   \n",
       "\n",
       "    amount_curr level        code        name  \\\n",
       "0  3.133333e+08     1        None  [-]项目成本(A)   \n",
       "1  3.189531e+08     2        A.01        开发成本   \n",
       "2  0.000000e+00     3     A.01.01        土地成本   \n",
       "3  0.000000e+00     4  A.01.01.01       政府土地费   \n",
       "4  0.000000e+00     9        None       土地出让金   \n",
       "\n",
       "                                  full_path  \n",
       "0                                [-]项目成本(A)  \n",
       "1                         [-]项目成本(A) > 开发成本  \n",
       "2                  [-]项目成本(A) > 开发成本 > 土地成本  \n",
       "3          [-]项目成本(A) > 开发成本 > 土地成本 > 政府土地费  \n",
       "4  [-]项目成本(A) > 开发成本 > 土地成本 > 政府土地费 > 土地出让金  \n",
       "\n",
       "[5 rows x 25 columns]"
      ]
     },
     "execution_count": 93,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "id": "14dc8d66",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "df = pd.read_excel('表4成本汇总表.xlsx')\n",
    "\n",
    "def process_subject_levels(df: pd.DataFrame) -> pd.DataFrame:\n",
    "    df = df.rename(columns={\"科目代码\": \"code\", \"成本科目名称\": \"subject_name\"})\n",
    "\n",
    "    df[\"code\"] = df[\"code\"].astype(str).str.strip()\n",
    "    df[\"level\"] = df[\"code\"].str.count(r\"\\.\") + 1\n",
    "\n",
    "    # 初始化四个层级列\n",
    "    df[\"level_1\"] = \"\"\n",
    "    df[\"level_2\"] = \"\"\n",
    "    df[\"level_3\"] = \"\"\n",
    "    df[\"level_4\"] = \"\"\n",
    "\n",
    "    df[\"full_path\"] = df[\"subject_name\"]  # 只使用当前名称作为 full_path\n",
    "\n",
    "    for idx, row in df.iterrows():\n",
    "        level = row[\"level\"]\n",
    "        subject_name = row[\"subject_name\"]\n",
    "\n",
    "        if level == 1:\n",
    "            df.at[idx, \"level_1\"] = subject_name\n",
    "        elif level == 2:\n",
    "            df.at[idx, \"level_2\"] = subject_name\n",
    "        elif level == 3:\n",
    "            df.at[idx, \"level_3\"] = subject_name\n",
    "        elif level == 4:\n",
    "            df.at[idx, \"level_4\"] = subject_name\n",
    "\n",
    "    return df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "id": "b6cea098",
   "metadata": {},
   "outputs": [],
   "source": [
    "temp_df = process_subject_levels(df)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "id": "660ff90d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>标记</th>\n",
       "      <th>code</th>\n",
       "      <th>subject_name</th>\n",
       "      <th>含税总成本（销售+持有）（万元）</th>\n",
       "      <th>不含税总成本（销售+持有）（万元）</th>\n",
       "      <th>总进税（销售+持有）（万元）</th>\n",
       "      <th>含税建面单方（元/m2）</th>\n",
       "      <th>含税可售单方（元/m2）</th>\n",
       "      <th>占总成本比例</th>\n",
       "      <th>level</th>\n",
       "      <th>level_1</th>\n",
       "      <th>level_2</th>\n",
       "      <th>level_3</th>\n",
       "      <th>level_4</th>\n",
       "      <th>full_path</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>01</td>\n",
       "      <td>开发成本</td>\n",
       "      <td>286714.291134</td>\n",
       "      <td>280973.841660</td>\n",
       "      <td>5740.449474</td>\n",
       "      <td>33096.768203</td>\n",
       "      <td>46396.391860</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>开发成本</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>开发成本</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>表4.1土地成本</td>\n",
       "      <td>01.01</td>\n",
       "      <td>土地成本</td>\n",
       "      <td>204093.793735</td>\n",
       "      <td>204093.436361</td>\n",
       "      <td>0.357375</td>\n",
       "      <td>23559.498748</td>\n",
       "      <td>33026.660767</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>土地成本</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>土地成本</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>表4.1土地成本</td>\n",
       "      <td>01.01.01</td>\n",
       "      <td>政府土地费</td>\n",
       "      <td>204093.793735</td>\n",
       "      <td>204093.436361</td>\n",
       "      <td>0.357375</td>\n",
       "      <td>23559.498748</td>\n",
       "      <td>33026.660767</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>政府土地费</td>\n",
       "      <td></td>\n",
       "      <td>政府土地费</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>表4.1土地成本</td>\n",
       "      <td>01.01.01.01</td>\n",
       "      <td>土地出让金</td>\n",
       "      <td>198000.000000</td>\n",
       "      <td>198000.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>22856.063708</td>\n",
       "      <td>32040.557002</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>土地出让金</td>\n",
       "      <td>土地出让金</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>表4.1土地成本</td>\n",
       "      <td>01.01.01.02</td>\n",
       "      <td>土地税费</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>土地税费</td>\n",
       "      <td>土地税费</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         标记         code subject_name  含税总成本（销售+持有）（万元）  不含税总成本（销售+持有）（万元）  \\\n",
       "0       NaN           01         开发成本     286714.291134      280973.841660   \n",
       "1  表4.1土地成本        01.01         土地成本     204093.793735      204093.436361   \n",
       "2  表4.1土地成本     01.01.01        政府土地费     204093.793735      204093.436361   \n",
       "3  表4.1土地成本  01.01.01.01        土地出让金     198000.000000      198000.000000   \n",
       "4  表4.1土地成本  01.01.01.02         土地税费          0.000000           0.000000   \n",
       "\n",
       "   总进税（销售+持有）（万元）  含税建面单方（元/m2）  含税可售单方（元/m2）  占总成本比例  level level_1 level_2  \\\n",
       "0     5740.449474  33096.768203  46396.391860     NaN      1    开发成本           \n",
       "1        0.357375  23559.498748  33026.660767     NaN      2            土地成本   \n",
       "2        0.357375  23559.498748  33026.660767     NaN      3                   \n",
       "3        0.000000  22856.063708  32040.557002     NaN      4                   \n",
       "4        0.000000      0.000000      0.000000     NaN      4                   \n",
       "\n",
       "  level_3 level_4 full_path  \n",
       "0                      开发成本  \n",
       "1                      土地成本  \n",
       "2   政府土地费             政府土地费  \n",
       "3           土地出让金     土地出让金  \n",
       "4            土地税费      土地税费  "
      ]
     },
     "execution_count": 87,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "b1c32d8d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "def process_subject_levels(df: pd.DataFrame) -> pd.DataFrame:\n",
    "    # 重命名列并清理代码\n",
    "    df = df.rename(columns={\"科目代码\": \"code\", \"成本科目名称\": \"subject_name\"})\n",
    "    df[\"code\"] = df[\"code\"].astype(str).str.strip()\n",
    "    df[\"level\"] = df[\"code\"].str.count(r\"\\.\") + 1\n",
    "\n",
    "    # 初始化层级列\n",
    "    df[[\"level_1\", \"level_2\", \"level_3\", \"level_4\"]] = \"\"\n",
    "\n",
    "    # 按代码排序\n",
    "    df = df.sort_values(\"code\").reset_index(drop=True)\n",
    "\n",
    "    # 存储父级信息\n",
    "    parent_map = {}\n",
    "\n",
    "    for idx, row in df.iterrows():\n",
    "        code = row[\"code\"]\n",
    "        level = row[\"level\"]\n",
    "        subject_name = row[\"subject_name\"]\n",
    "        parent_code = \".\".join(code.split(\".\")[:-1]) if \".\" in code else \"\"\n",
    "\n",
    "        # 获取父级信息\n",
    "        parent = parent_map.get(parent_code, {})\n",
    "        \n",
    "        # 设置层级列\n",
    "        if level == 1:\n",
    "            df.at[idx, \"level_1\"] = subject_name\n",
    "        elif level == 2:\n",
    "            df.at[idx, \"level_1\"] = parent.get(\"level_1\", \"\")\n",
    "            df.at[idx, \"level_2\"] = subject_name\n",
    "        elif level == 3:\n",
    "            df.at[idx, \"level_1\"] = parent.get(\"level_1\", \"\")\n",
    "            df.at[idx, \"level_2\"] = parent.get(\"level_2\", \"\")\n",
    "            df.at[idx, \"level_3\"] = subject_name\n",
    "        elif level == 4:\n",
    "            df.at[idx, \"level_1\"] = parent.get(\"level_1\", \"\")\n",
    "            df.at[idx, \"level_2\"] = parent.get(\"level_2\", \"\")\n",
    "            df.at[idx, \"level_3\"] = parent.get(\"level_3\", \"\")\n",
    "            df.at[idx, \"level_4\"] = subject_name\n",
    "\n",
    "        # 更新 parent_map\n",
    "        parent_map[code] = {\n",
    "            f\"level_{i}\": df.at[idx, f\"level_{i}\"] for i in range(1, 5)\n",
    "        }\n",
    "\n",
    "        # 设置 full_path\n",
    "        df.at[idx, \"full_path\"] = \" / \".join(\n",
    "            filter(None, [df.at[idx, f\"level_{i}\"] for i in range(1, 5)])\n",
    "        )\n",
    "\n",
    "    return df.sort_index()\n",
    "df = pd.read_excel('表4成本汇总表.xlsx')\n",
    "tem_df = process_subject_levels(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "58f7d00f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>标记</th>\n",
       "      <th>code</th>\n",
       "      <th>subject_name</th>\n",
       "      <th>含税总成本（销售+持有）（万元）</th>\n",
       "      <th>不含税总成本（销售+持有）（万元）</th>\n",
       "      <th>总进税（销售+持有）（万元）</th>\n",
       "      <th>含税建面单方（元/m2）</th>\n",
       "      <th>含税可售单方（元/m2）</th>\n",
       "      <th>占总成本比例</th>\n",
       "      <th>level</th>\n",
       "      <th>level_1</th>\n",
       "      <th>level_2</th>\n",
       "      <th>level_3</th>\n",
       "      <th>level_4</th>\n",
       "      <th>full_path</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>项目成本</td>\n",
       "      <td>295022.591456</td>\n",
       "      <td>289123.651416</td>\n",
       "      <td>5898.940040</td>\n",
       "      <td>34055.834069</td>\n",
       "      <td>47740.849285</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>项目成本</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>项目成本</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>01</td>\n",
       "      <td>开发成本</td>\n",
       "      <td>286714.291134</td>\n",
       "      <td>280973.841660</td>\n",
       "      <td>5740.449474</td>\n",
       "      <td>33096.768203</td>\n",
       "      <td>46396.391860</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>开发成本</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>开发成本</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>表4.1土地成本</td>\n",
       "      <td>01.01</td>\n",
       "      <td>土地成本</td>\n",
       "      <td>204093.793735</td>\n",
       "      <td>204093.436361</td>\n",
       "      <td>0.357375</td>\n",
       "      <td>23559.498748</td>\n",
       "      <td>33026.660767</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>开发成本</td>\n",
       "      <td>土地成本</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>开发成本 / 土地成本</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>表4.1土地成本</td>\n",
       "      <td>01.01.01</td>\n",
       "      <td>政府土地费</td>\n",
       "      <td>204093.793735</td>\n",
       "      <td>204093.436361</td>\n",
       "      <td>0.357375</td>\n",
       "      <td>23559.498748</td>\n",
       "      <td>33026.660767</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td>开发成本</td>\n",
       "      <td>土地成本</td>\n",
       "      <td>政府土地费</td>\n",
       "      <td></td>\n",
       "      <td>开发成本 / 土地成本 / 政府土地费</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>表4.1土地成本</td>\n",
       "      <td>01.01.01.01</td>\n",
       "      <td>土地出让金</td>\n",
       "      <td>198000.000000</td>\n",
       "      <td>198000.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>22856.063708</td>\n",
       "      <td>32040.557002</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td>开发成本</td>\n",
       "      <td>土地成本</td>\n",
       "      <td>政府土地费</td>\n",
       "      <td>土地出让金</td>\n",
       "      <td>开发成本 / 土地成本 / 政府土地费 / 土地出让金</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         标记         code subject_name  含税总成本（销售+持有）（万元）  不含税总成本（销售+持有）（万元）  \\\n",
       "0       NaN            0         项目成本     295022.591456      289123.651416   \n",
       "1       NaN           01         开发成本     286714.291134      280973.841660   \n",
       "2  表4.1土地成本        01.01         土地成本     204093.793735      204093.436361   \n",
       "3  表4.1土地成本     01.01.01        政府土地费     204093.793735      204093.436361   \n",
       "4  表4.1土地成本  01.01.01.01        土地出让金     198000.000000      198000.000000   \n",
       "\n",
       "   总进税（销售+持有）（万元）  含税建面单方（元/m2）  含税可售单方（元/m2）  占总成本比例  level level_1 level_2  \\\n",
       "0     5898.940040  34055.834069  47740.849285     NaN      1    项目成本           \n",
       "1     5740.449474  33096.768203  46396.391860     NaN      1    开发成本           \n",
       "2        0.357375  23559.498748  33026.660767     NaN      2    开发成本    土地成本   \n",
       "3        0.357375  23559.498748  33026.660767     NaN      3    开发成本    土地成本   \n",
       "4        0.000000  22856.063708  32040.557002     NaN      4    开发成本    土地成本   \n",
       "\n",
       "  level_3 level_4                    full_path  \n",
       "0                                         项目成本  \n",
       "1                                         开发成本  \n",
       "2                                  开发成本 / 土地成本  \n",
       "3   政府土地费                  开发成本 / 土地成本 / 政府土地费  \n",
       "4   政府土地费   土地出让金  开发成本 / 土地成本 / 政府土地费 / 土地出让金  "
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tem_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "id": "9c1dc27d",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "def process_subject_levels(df: pd.DataFrame) -> pd.DataFrame:\n",
    "    df = df.rename(columns={\"科目代码\": \"code\", \"成本科目名称\": \"subject_name\"})\n",
    "    df[\"code\"] = df[\"code\"].astype(str).str.strip()\n",
    "    df[\"level\"] = df[\"code\"].str.count(r\"\\.\") + 1\n",
    "\n",
    "    # 仅保留最多三级的数据\n",
    "    df = df[df[\"level\"] <= 3].copy()\n",
    "\n",
    "    # 初始化层级列\n",
    "    df[\"level_1\"] = \"\"\n",
    "    df[\"level_2\"] = \"\"\n",
    "    df[\"level_3\"] = \"\"\n",
    "\n",
    "    # 构建 code -> name 的映射表\n",
    "    code_name_map = dict(zip(df['code'], df['subject_name']))\n",
    "\n",
    "    # 初始化列\n",
    "    df['full_path'] = ''\n",
    "    hierarchy_cols = ['level_0', 'level_1', 'level_2', 'level_3']\n",
    "    for col in hierarchy_cols:\n",
    "        df[col] = ''\n",
    "\n",
    "    # 遍历一次，完成填充\n",
    "    for idx, row in df.iterrows():\n",
    "        code = row['code']\n",
    "        level = row['level']\n",
    "        subject_name = row['subject_name']\n",
    "\n",
    "        # 1. 填充当前层级（level_n）\n",
    "        if 0 <= level < len(hierarchy_cols):\n",
    "            df.at[idx, hierarchy_cols[level]] = subject_name\n",
    "\n",
    "        # 2. 构建父节点路径\n",
    "        if code:\n",
    "            parts = code.split('.')\n",
    "            path = []\n",
    "\n",
    "            for i in range(1, len(parts) + 1):\n",
    "                parent_code = '.'.join(parts[:i])\n",
    "                parent_name = code_name_map.get(parent_code, f\"未知({parent_code})\")\n",
    "                if i - 1 < len(hierarchy_cols):\n",
    "                    df.at[idx, hierarchy_cols[i - 1]] = parent_name\n",
    "                path.append(parent_name)\n",
    "\n",
    "            # 3. 填充 full_path\n",
    "            df.at[idx, 'full_path'] = ' > '.join(path)\n",
    "        return df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a9a6bbfe",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "id": "01f61f8c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.read_excel('表4成本汇总表.xlsx')\n",
    "df = df.rename(columns={\"科目代码\": \"code\", \"成本科目名称\": \"subject_name\"})\n",
    "df[\"code\"] = df[\"code\"].astype(str).str.strip()\n",
    "df[\"level\"] = df[\"code\"].str.count(r\"\\.\") + 1\n",
    "# 仅保留最多三级数据\n",
    "df = df[df[\"level\"] <= 4].copy()\n",
    "\n",
    "# 初始化层级列与 full_path\n",
    "df[\"level_1\"] = \"\"\n",
    "df[\"level_2\"] = \"\"\n",
    "df[\"level_3\"] = \"\"\n",
    "df[\"full_path\"] = \"\"\n",
    "\n",
    "# 构建 code -> subject_name 映射\n",
    "code_name_map = dict(zip(df[\"code\"], df[\"subject_name\"]))\n",
    "hierarchy_cols = ['level_1', 'level_2', 'level_3']\n",
    "\n",
    "for idx, row in df.iterrows():\n",
    "    code = row[\"code\"]\n",
    "    level = row[\"level\"]\n",
    "    parts = code.split(\".\")\n",
    "    subject_name = row['subject_name']\n",
    "\n",
    "    # 1. 填充当前层级（level_n）\n",
    "    if 0 <= level <= len(hierarchy_cols):\n",
    "        df.at[idx, hierarchy_cols[level-1]] = subject_name\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "id": "54da818a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>标记</th>\n",
       "      <th>code</th>\n",
       "      <th>subject_name</th>\n",
       "      <th>含税总成本（销售+持有）（万元）</th>\n",
       "      <th>不含税总成本（销售+持有）（万元）</th>\n",
       "      <th>总进税（销售+持有）（万元）</th>\n",
       "      <th>含税建面单方（元/m2）</th>\n",
       "      <th>含税可售单方（元/m2）</th>\n",
       "      <th>占总成本比例</th>\n",
       "      <th>level</th>\n",
       "      <th>level_1</th>\n",
       "      <th>level_2</th>\n",
       "      <th>level_3</th>\n",
       "      <th>full_path</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>01</td>\n",
       "      <td>开发成本</td>\n",
       "      <td>286714.291134</td>\n",
       "      <td>280973.841660</td>\n",
       "      <td>5740.449474</td>\n",
       "      <td>33096.768203</td>\n",
       "      <td>46396.391860</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "      <td>开发成本</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>表4.1土地成本</td>\n",
       "      <td>01.01</td>\n",
       "      <td>土地成本</td>\n",
       "      <td>204093.793735</td>\n",
       "      <td>204093.436361</td>\n",
       "      <td>0.357375</td>\n",
       "      <td>23559.498748</td>\n",
       "      <td>33026.660767</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td></td>\n",
       "      <td>土地成本</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>表4.1土地成本</td>\n",
       "      <td>01.01.01</td>\n",
       "      <td>政府土地费</td>\n",
       "      <td>204093.793735</td>\n",
       "      <td>204093.436361</td>\n",
       "      <td>0.357375</td>\n",
       "      <td>23559.498748</td>\n",
       "      <td>33026.660767</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>政府土地费</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>表4.1土地成本</td>\n",
       "      <td>01.01.01.01</td>\n",
       "      <td>土地出让金</td>\n",
       "      <td>198000.000000</td>\n",
       "      <td>198000.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>22856.063708</td>\n",
       "      <td>32040.557002</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>表4.1土地成本</td>\n",
       "      <td>01.01.01.02</td>\n",
       "      <td>土地税费</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         标记         code subject_name  含税总成本（销售+持有）（万元）  不含税总成本（销售+持有）（万元）  \\\n",
       "0       NaN           01         开发成本     286714.291134      280973.841660   \n",
       "1  表4.1土地成本        01.01         土地成本     204093.793735      204093.436361   \n",
       "2  表4.1土地成本     01.01.01        政府土地费     204093.793735      204093.436361   \n",
       "3  表4.1土地成本  01.01.01.01        土地出让金     198000.000000      198000.000000   \n",
       "4  表4.1土地成本  01.01.01.02         土地税费          0.000000           0.000000   \n",
       "\n",
       "   总进税（销售+持有）（万元）  含税建面单方（元/m2）  含税可售单方（元/m2）  占总成本比例  level level_1 level_2  \\\n",
       "0     5740.449474  33096.768203  46396.391860     NaN      1    开发成本           \n",
       "1        0.357375  23559.498748  33026.660767     NaN      2            土地成本   \n",
       "2        0.357375  23559.498748  33026.660767     NaN      3                   \n",
       "3        0.000000  22856.063708  32040.557002     NaN      4                   \n",
       "4        0.000000      0.000000      0.000000     NaN      4                   \n",
       "\n",
       "  level_3 full_path  \n",
       "0                    \n",
       "1                    \n",
       "2   政府土地费            \n",
       "3                    \n",
       "4                    "
      ]
     },
     "execution_count": 112,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "id": "ce18ecba",
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel('3.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "f43b151b",
   "metadata": {},
   "outputs": [],
   "source": [
    "tem_df.to_excel('2.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "3c6fed4d",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "from sqlalchemy import Column, Integer, String, Numeric,Float,DateTime\n",
    "from db_setting.base import Base\n",
    "import uuid\n",
    "from datetime import datetime\n",
    "class DynamicCostSummary(Base):\n",
    "    __tablename__ = \"dynamic_cost_summary\"\n",
    "\n",
    "    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))\n",
    "\n",
    "    subject_name = Column(String(200), default=\"\", comment=\"科目名称\")\n",
    "    category = Column(String(100), default=\"\", comment=\"类别\")\n",
    "    contract_status = Column(String(50), default=\"\", comment=\"合同状态\")\n",
    "\n",
    "    target_adjusted_cost = Column(Numeric(18, 2), default=0.0, comment=\"本次目标+调整成本\")\n",
    "    contract_signed_current = Column(Numeric(18, 2), default=0.0, comment=\"合同有效签约金额\")\n",
    "    signed_last = Column(Numeric(18, 2), default=0.0, comment=\"上次签约金额\")\n",
    "    signed_current = Column(Numeric(18, 2), default=0.0, comment=\"本次签约金额\")\n",
    "    change_last = Column(Numeric(18, 2), default=0.0, comment=\"上次变更金额\")\n",
    "    change_current = Column(Numeric(18, 2), default=0.0, comment=\"本次变更金额\")\n",
    "    pipeline_cost_last = Column(Numeric(18, 2), default=0.0, comment=\"上次在途成本\")\n",
    "    pipeline_cost_current = Column(Numeric(18, 2), default=0.0, comment=\"本次在途成本\")\n",
    "    contract_change_last = Column(Numeric(18, 2), default=0.0, comment=\"上次合同+变更(不含在途)\")\n",
    "    contract_change_current = Column(Numeric(18, 2), default=0.0, comment=\"本次合同+变更(不含在途)\")\n",
    "    estimate_change_last = Column(Numeric(18, 2), default=0.0, comment=\"上次预估变更\")\n",
    "    estimate_change_current = Column(Numeric(18, 2), default=0.0, comment=\"本次预估变更\")\n",
    "    pending_plan_last = Column(Numeric(18, 2), default=0.0, comment=\"上次待发生合约规划\")\n",
    "    pending_plan_current = Column(Numeric(18, 2), default=0.0, comment=\"本次待发生合约规划\")\n",
    "    dynamic_cost_last = Column(Numeric(18, 2), default=0.0, comment=\"上次动态成本\")\n",
    "    dynamic_cost_current = Column(Numeric(18, 2), default=0.0, comment=\"本次动态成本\")\n",
    "\n",
    "    code = Column(String(64), nullable=True)\n",
    "    level = Column(Integer, nullable=True)\n",
    "    full_path = Column(String(1000), nullable=True)\n",
    "    created_at = Column(DateTime, default=datetime.now)\n",
    "    level_0 = Column(String(200), default=\"\", comment=\"项目成本\")\n",
    "    level_1 = Column(String(200), default=\"\", comment=\"一级科目\")\n",
    "    level_2 = Column(String(200), default=\"\", comment=\"二级科目\")\n",
    "    level_3 = Column(String(200), default=\"\", comment=\"三级科目\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "52b5e767",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import re\n",
    "from db_setting.base import declarative_base,sessionmaker,engine\n",
    "import numpy as np\n",
    "import uuid\n",
    "from decimal import Decimal, InvalidOperation\n",
    "from sqlalchemy.schema import CreateTable\n",
    "from sqlalchemy.dialects import mysql \n",
    "\n",
    "\n",
    "SessionLocal = sessionmaker(bind=engine)\n",
    "db = SessionLocal()\n",
    "# --- 工具函数：金额清洗，转 float ---\n",
    "def clean_amount(amount_str):\n",
    "    if pd.isna(amount_str):\n",
    "        return 0.0\n",
    "    try:\n",
    "        clean_str = str(amount_str).replace(',', '').strip()\n",
    "        return float(clean_str)\n",
    "    except Exception:\n",
    "        return 0.0\n",
    "\n",
    "def clean_amount_fields(df, columns):\n",
    "    for col in columns:\n",
    "        if col in df.columns:\n",
    "            df[col] = df[col].apply(clean_amount)\n",
    "        else:\n",
    "            print(f\"字段未找到: {col}\")\n",
    "    return df\n",
    "\n",
    "\n",
    "def safe_decimal(val):\n",
    "    if val is None or val == \"\":\n",
    "        return Decimal(\"0.00\")\n",
    "    try:\n",
    "        val_str = str(val).replace(\",\", \"\").strip()\n",
    "        return Decimal(val_str)\n",
    "    except InvalidOperation:\n",
    "        return Decimal(\"0.00\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "id": "7ecd913c",
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "import pandas as pd\n",
    "\n",
    "def parse_row(row, indent_width=2):\n",
    "    \"\"\"\n",
    "    根据编码识别层级结构，提取特殊符号及其内容，只保留类别为“科目”的行。\n",
    "\n",
    "    Args:\n",
    "        row: pandas DataFrame 的一行数据\n",
    "        indent_width: 每多少个空格算作一个缩进层级，默认每两个空格算一级（未使用）\n",
    "\n",
    "    Returns:\n",
    "        tuple(level, code, name, symbol, symbol_content) 或 None（如果不是“科目”）\n",
    "    \"\"\"\n",
    "    # 获取字段\n",
    "    if isinstance(row, dict):\n",
    "        subject_name = str(row.get('科目名称', '')).rstrip()\n",
    "        category = str(row.get('类别', ''))\n",
    "    else:\n",
    "        subject_name = str(row['科目名称']).rstrip()\n",
    "        category = str(row['类别'])\n",
    "\n",
    "    # 只保留“科目”类别的行\n",
    "    if category != '科目':\n",
    "        return None\n",
    "\n",
    "    # 提取编码和名称\n",
    "    # 匹配括号中的编码，如 (A), (A.01), (A.01.01), (A.01.05.01)\n",
    "    code_match = re.search(r'\\((.*?)\\)$', subject_name)\n",
    "    if code_match:\n",
    "        code = code_match.group(1).strip()  # 提取括号中的编码\n",
    "        # 去除编码部分\n",
    "        name = re.sub(r'\\s*\\(.*?\\)$', '', subject_name).strip()\n",
    "    else:\n",
    "        code = \"\"\n",
    "        name = subject_name.strip()\n",
    "\n",
    "    # 去除特殊符号，获取纯文本名称\n",
    "    name = re.sub(r'^\\[[\\+\\-]\\]\\s*', '', name).strip()\n",
    "\n",
    "    # 根据编码中的点号计算层级\n",
    "    if code:\n",
    "        level = code.count('.')  # 点号数量决定层级\n",
    "    else:\n",
    "        level = ''  # 无编码时，默认为 level 0\n",
    "\n",
    "    return level, code, name\n",
    "\n",
    "def solve_data_dynamic(df):\n",
    "    print(\"==========开始数据清洗============\")\n",
    "\n",
    "    # 解析行并过滤非“科目”\n",
    "    parsed = df.apply(parse_row, axis=1)\n",
    "    df_parsed = parsed[parsed.notna()]\n",
    "    df = df.loc[df_parsed.index].copy()\n",
    "\n",
    "    # 拆分结果\n",
    "    df[['level', 'code', 'name']] = pd.DataFrame(df_parsed.tolist(), index=df_parsed.index)\n",
    "\n",
    "    # 初始化层级列\n",
    "    hierarchy_cols = ['level_0', 'level_1', 'level_2', 'level_3']\n",
    "    for col in hierarchy_cols:\n",
    "        df[col] = ''\n",
    "    df['full_path'] = ''\n",
    "\n",
    "    # 跟踪每个层级最近的科目名称\n",
    "    last_seen = {0: '', 1: '', 2: '', 3: ''}\n",
    "\n",
    "    # 按表格顺序处理每行\n",
    "    for idx, row in df.iterrows():\n",
    "        level = row['level']\n",
    "        name = row['name']\n",
    "\n",
    "        # 更新当前层级的名称\n",
    "        last_seen[level] = name\n",
    "\n",
    "        # 填充 level_0 到 level_3\n",
    "        for i in range(4):\n",
    "            if i == level:\n",
    "                df.at[idx, f'level_{i}'] = name  # 当前层级使用当前名称\n",
    "            elif i < level:\n",
    "                df.at[idx, f'level_{i}'] = last_seen.get(i, '')  # 使用最近的父级名称\n",
    "            else:\n",
    "                df.at[idx, f'level_{i}'] = ''  # 高于当前层级的为空\n",
    "\n",
    "        # 填充 full_path\n",
    "        hierarchy = [last_seen.get(i, '') for i in range(level + 1) if last_seen.get(i, '')]\n",
    "        df.at[idx, 'full_path'] = ' > '.join(hierarchy)\n",
    "\n",
    "    # 确保保留原始列并添加新\n",
    "    return df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 83,
   "id": "33a92bc3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "==========开始数据清洗============\n"
     ]
    }
   ],
   "source": [
    "df = pd.read_excel('动态成本.xlsx')\n",
    "temp = solve_data_dynamic(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 81,
   "id": "5fa76e0f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>科目名称</th>\n",
       "      <th>类别</th>\n",
       "      <th>本次目标+调整成本</th>\n",
       "      <th>合同状态</th>\n",
       "      <th>合同有效签约金额</th>\n",
       "      <th>上次签约金额</th>\n",
       "      <th>本次签约金额</th>\n",
       "      <th>上次变更金额</th>\n",
       "      <th>本次变更金额</th>\n",
       "      <th>上次在途成本</th>\n",
       "      <th>...</th>\n",
       "      <th>上次动态成本</th>\n",
       "      <th>本次动态成本</th>\n",
       "      <th>level</th>\n",
       "      <th>code</th>\n",
       "      <th>name</th>\n",
       "      <th>level_0</th>\n",
       "      <th>level_1</th>\n",
       "      <th>level_2</th>\n",
       "      <th>level_3</th>\n",
       "      <th>full_path</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>[-]项目成本(A)</td>\n",
       "      <td>科目</td>\n",
       "      <td>1,331,975,113.55</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>313,189,317.30</td>\n",
       "      <td>313,333,317.30</td>\n",
       "      <td>598,351.75</td>\n",
       "      <td>878,837.01</td>\n",
       "      <td>80,800.15</td>\n",
       "      <td>...</td>\n",
       "      <td>855805731.87</td>\n",
       "      <td>855805731.87</td>\n",
       "      <td>0</td>\n",
       "      <td>A</td>\n",
       "      <td>项目成本</td>\n",
       "      <td>项目成本</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>项目成本</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>[-]开发成本(A.01)</td>\n",
       "      <td>科目</td>\n",
       "      <td>1,133,385,888.98</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>318,953,138.05</td>\n",
       "      <td>318,953,138.05</td>\n",
       "      <td>598,351.75</td>\n",
       "      <td>878,837.01</td>\n",
       "      <td>80,800.15</td>\n",
       "      <td>...</td>\n",
       "      <td>848198198.08</td>\n",
       "      <td>848198198.08</td>\n",
       "      <td>1</td>\n",
       "      <td>A.01</td>\n",
       "      <td>开发成本</td>\n",
       "      <td>项目成本</td>\n",
       "      <td>开发成本</td>\n",
       "      <td></td>\n",
       "      <td></td>\n",
       "      <td>项目成本 &gt; 开发成本</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>[-]土地成本(A.01.01)</td>\n",
       "      <td>科目</td>\n",
       "      <td>9,189,388.90</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>9,189,388.90</td>\n",
       "      <td>9,189,388.90</td>\n",
       "      <td>2</td>\n",
       "      <td>A.01.01</td>\n",
       "      <td>土地成本</td>\n",
       "      <td>项目成本</td>\n",
       "      <td>开发成本</td>\n",
       "      <td>土地成本</td>\n",
       "      <td></td>\n",
       "      <td>项目成本 &gt; 开发成本 &gt; 土地成本</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>[+]政府土地费(A.01.01.01)</td>\n",
       "      <td>科目</td>\n",
       "      <td>9,189,388.90</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>9,189,388.90</td>\n",
       "      <td>9,189,388.90</td>\n",
       "      <td>3</td>\n",
       "      <td>A.01.01.01</td>\n",
       "      <td>政府土地费</td>\n",
       "      <td>项目成本</td>\n",
       "      <td>开发成本</td>\n",
       "      <td>土地成本</td>\n",
       "      <td>政府土地费</td>\n",
       "      <td>项目成本 &gt; 开发成本 &gt; 土地成本 &gt; 政府土地费</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>[-]前期工程费(A.01.01)</td>\n",
       "      <td>科目</td>\n",
       "      <td>19,355,155.18</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>8,773,078.50</td>\n",
       "      <td>8,773,078.50</td>\n",
       "      <td>-7,197.09</td>\n",
       "      <td>-7,197.09</td>\n",
       "      <td>0.00</td>\n",
       "      <td>...</td>\n",
       "      <td>19,158,085.83</td>\n",
       "      <td>19,131,038.83</td>\n",
       "      <td>2</td>\n",
       "      <td>A.01.01</td>\n",
       "      <td>前期工程费</td>\n",
       "      <td>项目成本</td>\n",
       "      <td>开发成本</td>\n",
       "      <td>前期工程费</td>\n",
       "      <td></td>\n",
       "      <td>项目成本 &gt; 开发成本 &gt; 前期工程费</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 27 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                         科目名称  类别         本次目标+调整成本 合同状态 合同有效签约金额  \\\n",
       "0                  [-]项目成本(A)  科目  1,331,975,113.55  NaN      NaN   \n",
       "1             　　[-]开发成本(A.01)  科目  1,133,385,888.98  NaN      NaN   \n",
       "2        　　　　[-]土地成本(A.01.01)  科目      9,189,388.90  NaN      NaN   \n",
       "3  　　　　　　[+]政府土地费(A.01.01.01)  科目      9,189,388.90  NaN      NaN   \n",
       "7       　　　　[-]前期工程费(A.01.01)  科目     19,355,155.18  NaN      NaN   \n",
       "\n",
       "           上次签约金额          本次签约金额      上次变更金额      本次变更金额     上次在途成本  ...  \\\n",
       "0  313,189,317.30  313,333,317.30  598,351.75  878,837.01  80,800.15  ...   \n",
       "1  318,953,138.05  318,953,138.05  598,351.75  878,837.01  80,800.15  ...   \n",
       "2            0.00            0.00        0.00        0.00       0.00  ...   \n",
       "3            0.00            0.00        0.00        0.00       0.00  ...   \n",
       "7    8,773,078.50    8,773,078.50   -7,197.09   -7,197.09       0.00  ...   \n",
       "\n",
       "          上次动态成本         本次动态成本 level        code   name level_0 level_1  \\\n",
       "0   855805731.87   855805731.87     0           A   项目成本    项目成本           \n",
       "1   848198198.08   848198198.08     1        A.01   开发成本    项目成本    开发成本   \n",
       "2   9,189,388.90   9,189,388.90     2     A.01.01   土地成本    项目成本    开发成本   \n",
       "3   9,189,388.90   9,189,388.90     3  A.01.01.01  政府土地费    项目成本    开发成本   \n",
       "7  19,158,085.83  19,131,038.83     2     A.01.01  前期工程费    项目成本    开发成本   \n",
       "\n",
       "  level_2 level_3                   full_path  \n",
       "0                                        项目成本  \n",
       "1                                 项目成本 > 开发成本  \n",
       "2    土地成本                  项目成本 > 开发成本 > 土地成本  \n",
       "3    土地成本   政府土地费  项目成本 > 开发成本 > 土地成本 > 政府土地费  \n",
       "7   前期工程费                 项目成本 > 开发成本 > 前期工程费  \n",
       "\n",
       "[5 rows x 27 columns]"
      ]
     },
     "execution_count": 81,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "temp.head()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "id": "c0141052",
   "metadata": {},
   "outputs": [],
   "source": [
    "temp.to_excel('1.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2de4425e",
   "metadata": {},
   "source": [
    "### 测试总表——sheet1\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "id": "53657fd0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 读取 Excel 文件的第一个 sheet\n",
    "file_path = '7.2-项目目标成本测算表格-V2.xlsx'\n",
    "df = pd.read_excel(file_path, sheet_name=0)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "f37a7f8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "headers = df.columns.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "id": "c1aa1819",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'编号： SYJT-BF-DC04-QR02\\n版号：A/2\\n生效日期：2022年12月23日'"
      ]
     },
     "execution_count": 124,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "headers[2]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7b91058c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "原始数据前5行：\n",
      "       0           1                                                  2\n",
      "0    NaN  项目目标成本测算表格  编号： SYJT-BF-DC04-QR02&#10;版号：A/2&#10;生效日期：2022...\n",
      "1    NaN         NaN                                                NaN\n",
      "2    NaN  项目目标成本测算表格                                                NaN\n",
      "3    NaN         NaN                                                NaN\n",
      "4    NaN         NaN                                                NaN\n",
      "5  项目名称：     深业XXX项目                                                NaN\n",
      "6  项目区域：          xx                                                NaN\n",
      "7    版本：         执行版                                                NaN\n",
      "8    NaN         NaN                                                NaN\n",
      "9    NaN         NaN                                                NaN\n",
      "\n",
      "提取结果：\n",
      "项目名称：深业XXX项目\n",
      "项目区域：xx\n",
      "版本：执行版\n",
      "编制单位：xx单位\n",
      "编制日期：2025.05.15\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 读取 Excel 文件的第一个 sheet\n",
    "file_path = '7.2-项目目标成本测算表格-V2.xlsx'\n",
    "df = pd.read_excel(file_path, sheet_name=0, header=None)  # 不自动识别表头\n",
    "\n",
    "# 查看前几行数据（调试用）\n",
    "print(\"原始数据前10行：\")\n",
    "print(df.head(10))\n",
    "\n",
    "# 要提取的关键字段（注意末尾带冒号）\n",
    "target_keys = ['项目名称：', '项目区域：', '版本：', '编制单位：', '编制日期：']\n",
    "\n",
    "# 存储结果的字典\n",
    "result = {key.strip('：'): None for key in target_keys}  # 去掉冒号作为键名\n",
    "\n",
    "# 遍历第一列（索引为0），寻找匹配的关键词，并从第二列（索引为1）提取值\n",
    "for index, row in df.iterrows():\n",
    "    if len(row) < 2:\n",
    "        continue  # 忽略长度不足的行\n",
    "\n",
    "    cell = str(row[0])  # 第一列是字段名\n",
    "    value = row[1]      # 第二列是对应值\n",
    "\n",
    "    for key in target_keys:\n",
    "        if cell.startswith(key):\n",
    "            result_key = key.strip('：')  # 去除冒号，用于保存到字典\n",
    "            result[result_key] = value\n",
    "            break  # 匹配成功后跳出循环\n",
    "\n",
    "# 打印结果\n",
    "print(\"\\n提取结果：\")\n",
    "for k, v in result.items():\n",
    "    print(f\"{k}：{v}\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "cf8aab31",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "id": "c6bfacc4",
   "metadata": {},
   "outputs": [],
   "source": [
    "import re\n",
    "\n",
    "def add_ddl_details(ddl: str):\n",
    "    table_name = extract_table_name(ddl)\n",
    "    columns = extract_columns_from_ddl(ddl)\n",
    "\n",
    "    for col in columns:\n",
    "        doc = f\"表 `{table_name}` 中字段 `{col['name']}` 类型为 {col['type']}，含义：{col['comment']}。\"\n",
    "        metadata = {\n",
    "            \"table\": table_name,\n",
    "            \"column\": col[\"name\"],\n",
    "            \"type\": \"column\",\n",
    "            \"comment\": col[\"comment\"]\n",
    "        }\n",
    "        print(doc)\n",
    "        print('*'*50)\n",
    "        print(metadata)\n",
    "def extract_table_name( ddl: str) -> str:\n",
    "    match = re.search(r'CREATE\\s+TABLE\\s+`?(\\w+)`?', ddl, re.IGNORECASE)\n",
    "    return match.group(1) if match else \"unknown_table\"\n",
    "\n",
    "def extract_columns_from_ddl(ddl: str) -> list:\n",
    "    columns = []\n",
    "\n",
    "    # 提取括号内的列定义部分\n",
    "    bracket_content_match = re.search(r'\\((.*)\\)', ddl, re.DOTALL)\n",
    "    if not bracket_content_match:\n",
    "        return columns  # 没有匹配到括号内容，返回空列表\n",
    "\n",
    "    columns_part = bracket_content_match.group(1)\n",
    "\n",
    "    # 按逗号分割行，排除行尾注释和换行符\n",
    "    lines = [line.strip() for line in columns_part.split(',') if line.strip()]\n",
    "\n",
    "    # 匹配列定义的正则，排除常见约束关键字开头的行（比如 PRIMARY KEY, UNIQUE, CONSTRAINT, FOREIGN KEY 等）\n",
    "    column_pattern = re.compile(\n",
    "        r'^`?(\\w+)`?\\s+([^\\s]+(?:\\([^\\)]*\\))?)(?:\\s+COMMENT\\s+\\'([^\\']*)\\')?', re.IGNORECASE\n",
    "    )\n",
    "\n",
    "    exclude_keywords = ['PRIMARY', 'UNIQUE', 'CONSTRAINT', 'FOREIGN', 'KEY', 'INDEX', 'CHECK']\n",
    "\n",
    "    for line in lines:\n",
    "        # 跳过以约束关键字开头的行\n",
    "        if any(line.upper().startswith(kw) for kw in exclude_keywords):\n",
    "            continue\n",
    "\n",
    "        m = column_pattern.match(line)\n",
    "        if m:\n",
    "            name, col_type, comment = m.groups()\n",
    "            columns.append({\n",
    "                \"name\": name,\n",
    "                \"type\": col_type,\n",
    "                \"comment\": comment or \"\"\n",
    "            })\n",
    "\n",
    "    return columns\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "4c6f87c5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "表 `dynamic_cost_summary` 中字段 `id` 类型为 VARCHAR(36)，含义：。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'id', 'type': 'column', 'comment': ''}\n",
      "表 `dynamic_cost_summary` 中字段 `subject_name` 类型为 VARCHAR(200)，含义：科目名称。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'subject_name', 'type': 'column', 'comment': '科目名称'}\n",
      "表 `dynamic_cost_summary` 中字段 `category` 类型为 VARCHAR(100)，含义：类别。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'category', 'type': 'column', 'comment': '类别'}\n",
      "表 `dynamic_cost_summary` 中字段 `contract_status` 类型为 VARCHAR(50)，含义：合同状态。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'contract_status', 'type': 'column', 'comment': '合同状态'}\n",
      "表 `dynamic_cost_summary` 中字段 `target_adjusted_cost` 类型为 NUMERIC(18，含义：。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'target_adjusted_cost', 'type': 'column', 'comment': ''}\n",
      "表 `dynamic_cost_summary` 中字段 `contract_signed_current` 类型为 NUMERIC(18，含义：。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'contract_signed_current', 'type': 'column', 'comment': ''}\n",
      "表 `dynamic_cost_summary` 中字段 `signed_last` 类型为 NUMERIC(18，含义：。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'signed_last', 'type': 'column', 'comment': ''}\n",
      "表 `dynamic_cost_summary` 中字段 `signed_current` 类型为 NUMERIC(18，含义：。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'signed_current', 'type': 'column', 'comment': ''}\n",
      "表 `dynamic_cost_summary` 中字段 `change_last` 类型为 NUMERIC(18，含义：。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'change_last', 'type': 'column', 'comment': ''}\n",
      "表 `dynamic_cost_summary` 中字段 `change_current` 类型为 NUMERIC(18，含义：。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'change_current', 'type': 'column', 'comment': ''}\n",
      "表 `dynamic_cost_summary` 中字段 `pipeline_cost_last` 类型为 NUMERIC(18，含义：。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'pipeline_cost_last', 'type': 'column', 'comment': ''}\n",
      "表 `dynamic_cost_summary` 中字段 `pipeline_cost_current` 类型为 NUMERIC(18，含义：。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'pipeline_cost_current', 'type': 'column', 'comment': ''}\n",
      "表 `dynamic_cost_summary` 中字段 `contract_change_last` 类型为 NUMERIC(18，含义：。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'contract_change_last', 'type': 'column', 'comment': ''}\n",
      "表 `dynamic_cost_summary` 中字段 `contract_change_current` 类型为 NUMERIC(18，含义：。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'contract_change_current', 'type': 'column', 'comment': ''}\n",
      "表 `dynamic_cost_summary` 中字段 `estimate_change_last` 类型为 NUMERIC(18，含义：。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'estimate_change_last', 'type': 'column', 'comment': ''}\n",
      "表 `dynamic_cost_summary` 中字段 `estimate_change_current` 类型为 NUMERIC(18，含义：。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'estimate_change_current', 'type': 'column', 'comment': ''}\n",
      "表 `dynamic_cost_summary` 中字段 `pending_plan_last` 类型为 NUMERIC(18，含义：。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'pending_plan_last', 'type': 'column', 'comment': ''}\n",
      "表 `dynamic_cost_summary` 中字段 `pending_plan_current` 类型为 NUMERIC(18，含义：。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'pending_plan_current', 'type': 'column', 'comment': ''}\n",
      "表 `dynamic_cost_summary` 中字段 `dynamic_cost_last` 类型为 NUMERIC(18，含义：。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'dynamic_cost_last', 'type': 'column', 'comment': ''}\n",
      "表 `dynamic_cost_summary` 中字段 `dynamic_cost_current` 类型为 NUMERIC(18，含义：。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'dynamic_cost_current', 'type': 'column', 'comment': ''}\n",
      "表 `dynamic_cost_summary` 中字段 `code` 类型为 VARCHAR(64)，含义：。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'code', 'type': 'column', 'comment': ''}\n",
      "表 `dynamic_cost_summary` 中字段 `level` 类型为 INTEGER，含义：。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'level', 'type': 'column', 'comment': ''}\n",
      "表 `dynamic_cost_summary` 中字段 `full_path` 类型为 VARCHAR(1000)，含义：。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'full_path', 'type': 'column', 'comment': ''}\n",
      "表 `dynamic_cost_summary` 中字段 `created_at` 类型为 DATETIME，含义：。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'created_at', 'type': 'column', 'comment': ''}\n",
      "表 `dynamic_cost_summary` 中字段 `level_0` 类型为 VARCHAR(200)，含义：项目成本。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'level_0', 'type': 'column', 'comment': '项目成本'}\n",
      "表 `dynamic_cost_summary` 中字段 `level_1` 类型为 VARCHAR(200)，含义：一级科目。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'level_1', 'type': 'column', 'comment': '一级科目'}\n",
      "表 `dynamic_cost_summary` 中字段 `level_2` 类型为 VARCHAR(200)，含义：二级科目。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'level_2', 'type': 'column', 'comment': '二级科目'}\n",
      "表 `dynamic_cost_summary` 中字段 `level_3` 类型为 VARCHAR(200)，含义：三级科目。\n",
      "**************************************************\n",
      "{'table': 'dynamic_cost_summary', 'column': 'level_3', 'type': 'column', 'comment': '三级科目'}\n"
     ]
    }
   ],
   "source": [
    "ddl = \"\"\"\n",
    "CREATE TABLE dynamic_cost_summary (\n",
    "\tid VARCHAR(36) NOT NULL, \n",
    "\tsubject_name VARCHAR(200) COMMENT '科目名称', \n",
    "\tcategory VARCHAR(100) COMMENT '类别', \n",
    "\tcontract_status VARCHAR(50) COMMENT '合同状态', \n",
    "\ttarget_adjusted_cost NUMERIC(18, 2) COMMENT '本次目标+调整成本', \n",
    "\tcontract_signed_current NUMERIC(18, 2) COMMENT '合同有效签约金额', \n",
    "\tsigned_last NUMERIC(18, 2) COMMENT '上次签约金额', \n",
    "\tsigned_current NUMERIC(18, 2) COMMENT '本次签约金额', \n",
    "\tchange_last NUMERIC(18, 2) COMMENT '上次变更金额', \n",
    "\tchange_current NUMERIC(18, 2) COMMENT '本次变更金额', \n",
    "\tpipeline_cost_last NUMERIC(18, 2) COMMENT '上次在途成本', \n",
    "\tpipeline_cost_current NUMERIC(18, 2) COMMENT '本次在途成本', \n",
    "\tcontract_change_last NUMERIC(18, 2) COMMENT '上次合同+变更(不含在途)', \n",
    "\tcontract_change_current NUMERIC(18, 2) COMMENT '本次合同+变更(不含在途)', \n",
    "\testimate_change_last NUMERIC(18, 2) COMMENT '上次预估变更', \n",
    "\testimate_change_current NUMERIC(18, 2) COMMENT '本次预估变更', \n",
    "\tpending_plan_last NUMERIC(18, 2) COMMENT '上次待发生合约规划', \n",
    "\tpending_plan_current NUMERIC(18, 2) COMMENT '本次待发生合约规划', \n",
    "\tdynamic_cost_last NUMERIC(18, 2) COMMENT '上次动态成本', \n",
    "\tdynamic_cost_current NUMERIC(18, 2) COMMENT '本次动态成本', \n",
    "\tcode VARCHAR(64), \n",
    "\tlevel INTEGER, \n",
    "\tfull_path VARCHAR(1000), \n",
    "\tcreated_at DATETIME, \n",
    "\tlevel_0 VARCHAR(200) COMMENT '项目成本', \n",
    "\tlevel_1 VARCHAR(200) COMMENT '一级科目', \n",
    "\tlevel_2 VARCHAR(200) COMMENT '二级科目', \n",
    "\tlevel_3 VARCHAR(200) COMMENT '三级科目', \n",
    "\tPRIMARY KEY (id)\n",
    ")\n",
    "\n",
    "\"\"\"\n",
    "add_ddl_details(ddl)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
